SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

PRINT '---- tblTechnicalCommissioner'

PRINT '-- Create'
--------------------------------------------------------------------
--
--------------------------------------------------------------------

PRINT '-- Table title'
INSERT tblUFGTableTitles (utt_Tablename, utt_Languagename, utt_Title, utt_Description)
VALUES ('tblTechnicalCommissioner','Nederlands','Technische commissie','Gegevens technische commissie')

PRINT '-- Column titles'
INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblTechnicalCommissioner','tco_PersonID','Nederlands','Lidnummer','U dient een lid te selecteren.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblTechnicalCommissioner','tco_ClubName','Nederlands','Clubnaam','U dient een clubnaam in te vullen.')

UPDATE tblUFGForeignKeyColumns
SET ufc_AdditionalWhere = 'dbo.fnGetClubnameByUser(@UFGUsername) IN (clb_Name, ''*'')'
WHERE ufc_ForeignKeyName = 'FK_tco_ClubName'
AND ufc_TableName = 'tblTechnicalCommissioner'
AND ufc_ColumnName = 'tco_ClubName'
AND ufc_ReferenceTableName = 'tblClubs'
AND ufc_ReferenceColumnName = 'clb_Name'

DROP PROCEDURE [dbo].[sptblTechnicalCommissionerINSERT]
GO
CREATE PROCEDURE [dbo].[sptblTechnicalCommissionerINSERT]
	-- Add the parameters for the stored procedure here
	@tco_PersonID int,
	@tco_ClubName varchar(20)
AS
BEGIN    
	DECLARE @TranCount int
	SET @TranCount = @@TRANCOUNT 
	IF @TranCount > 0
		SAVE TRAN sptblTechnicalCommissionerINSERT
	ELSE
		BEGIN TRAN
		
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	-- REPEATABLE READ noodzakelijk, omdat de username van betreffende personen
	-- kan worden gewijzigd in een andere transactie
	
	SET NOCOUNT ON;
	BEGIN TRY
	IF NOT EXISTS (
		SELECT prs_Username FROM dbo.tblPersons 		
		WHERE NOT prs_Username is null
		AND prs_ID = @tco_PersonID
	)
	BEGIN
	RAISERROR ('UsernameRequired', 16, 1)	
	END
	
	DECLARE @RoleName varchar(50)
	SET @RoleName = 'Technische Commissie Club'
	INSERT INTO tblUFGUsersInRoles (uir_UserName,uir_RoleName)	
	SELECT prs_UserName, @RoleName FROM dbo.tblPersons 
	WHERE prs_ID = @tco_PersonID
	
	INSERT INTO tblTechnicalCommissioner
	(tco_PersonID, tco_ClubName)
	VALUES (@tco_PersonID, @tco_ClubName)

	IF @TranCount = 0
		COMMIT TRAN	
		
	END TRY
	BEGIN CATCH
		DECLARE @ErrNo int
		DECLARE @ErrMsg nvarchar(2048)
		DECLARE @ErrSeverity int
		DECLARE @ErrState int
		
		SELECT @ErrNo = ERROR_NUMBER(),
			@ErrMsg = ERROR_MESSAGE(),
			@ErrSeverity = ERROR_SEVERITY(),
			@ErrState = ERROR_STATE()	
		
		RAISERROR @ErrNo @ErrMsg
		
		IF @TranCount > 0
			ROLLBACK TRAN sptblTechnicalCommissionerINSERT
		ELSE
			ROLLBACK TRAN
	END CATCH 
END
GO

UPDATE tblUFGTables SET utb_Insertsp = 'sptblTechnicalCommissionerINSERT' 
WHERE utb_TableName = 'tblTechnicalCommissioner'
GO

CREATE TRIGGER tblTechnicalCommissioner_Delete
   ON  dbo.tblTechnicalCommissioner
   AFTER DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    BEGIN TRY
	
	DECLARE @RoleName varchar(50)
	SET @RoleName = 'Technische Commissie Club'	
		
	DELETE FROM tblUFGUsersInRoles	
	WHERE uir_RoleName = @RoleName AND uir_UserName IN (
		SELECT prs_Username FROM tblPersons
		JOIN deleted ON prs_ID = deleted.tco_PersonID
	)		
		
	END TRY
	BEGIN CATCH
		DECLARE @ErrNo int
		DECLARE @ErrMsg nvarchar(2048)
		DECLARE @ErrSeverity int
		DECLARE @ErrState int
		
		SELECT @ErrNo = ERROR_NUMBER(),
			@ErrMsg = ERROR_MESSAGE(),
			@ErrSeverity = ERROR_SEVERITY(),
			@ErrState = ERROR_STATE()	
		
		RAISERROR @ErrNo @ErrMsg
	END CATCH 

END
GO